JCapper Message Board

          JCapper 101
                      -- WagerHistory Module SQL Reports

• Download HDW chart result files for the previous day, the current day's HDW V4 .JCP files, and run a DBBuild in Mode 5.



• You want to bring your database current so that data for the horses on the tickets you are about to workup is present in your Starterhistory table.

• Key the previous day's tickets into Screen 1 of the WagerHistory Module as described in the Help Doc.



• Or alternately, download a .csv file from the NHPlay site containing wager detail for tickets submitted the previous day, and perform Ticket Workup using Screen 2 of the WagerHistory Module as described below.



• Launch the WagerHistory Module and click Data Entry on the Main Menu.



• On the WagerHistory Module Data Entry Sheet click the CSV File Ticket Import button.
  
  
  Note: I hadn't entered tickets using Screen 2 in a few years and decided to do it that way instead of keying tickets manually using Screen 1 which in hindsight would have been easier.
  
  Because I quickly discovered:
  
  The last time I added new code to the WagerHistory Module was 2017. At the time the .csv files I was downloading from the NHPlay site were 12 columns wide.
  
  The .csv file I downloaded Wed morning from the NHPlay site was 15 columns wide and the file wouldn't import without without throwing a type mismatch error.
  
  Opened the new .csv file in Notepad, mapped out the data that was in each column, and spent the next few hours coding out a new import routine. Also added a new option to the .CSV file type drop down that enables the user to select and persist the new 15 column .csv file type.
  
  Tested the changes and compiled a new WagerHistory Module that will be in the next JCapper Program Update I publish.
  
  
  
• Work through each of the imported tickets one at a time using the CSV File Ticket Import Screen (also known as Screen 2 of the WagerHistory Module.)

1. Select the Primary Horse for each ticket:
   
   
   1. Click the Saddle Cloth button provided the saddle cloth number for primary horse on the ticket is displayed on the button.
   
   
   
   2. If the Saddle Cloth button is not displaying the Primary Horse on the ticket:
      
      Refresh the Horse drop down for the current race by clicking the Refresh button with the "<<" characters on it located just above the Horse drop down.
      
      Then open up the the Horse drop down and select the Primary Horse.
   
   
   
   3. Selecting the Primary Horse causes the Interface to auto generate a Situational Queries Report for that horse. Similar to Prob Expressions (but in this case "canned" Prob Expressions.)
   
   
2. Hit the Apply button on the bottom right of the Situational Queries Report (with the apply box checked) to write the scored query results for the Primary Horse to the Things table. (More on that later.)



3. Manually add a Short Descr code and other user defined data points such as Horse Physicality, etc. from your notes.



4. Double check your work, check the Ticket Ready box, and hit the Save button.



5. Hit the Next button to bring up the next ticket. (You can also use the Back button to scroll back if you want to look at previous tickets.)
   
   From there, jump to step 1 above and repeat until there are no more tickets to be worked up.

1. Manual Ticket Data Entry using Screen 1 of the WagerHistory Module.


2. CSV Import and Ticket Workup using Screen 2 of the WagerHistory Module.

1. Launch the WagerHistory Module and click Reports on the Main Menu to launch the WagerHistory Reports Screen.



2. Click the SQL button located in the lower left hand corner of the WagerHistory Reports Screen to launch the WagerHistory Module SQL Expression Tool.




3. Creating a New SQL Expression: Click the Assist button if you want the Interface to paste a default sql expression into the main textbox of WagerHistory Module SQL Expression Tool.
   
   Screenshot:
   
   
   
   The above screenshot was taken just after clicking the Assist button.
   
   I've highlighted the the defaults in the sql expression created by the Interface for ShortDescription, UDMName, EstimatedEdge, and Dates.
   
   Your job is to replace the highlighted defaults with your own actual ShortDescr code(s), UDMName(s), EstimatedEdge, Start and End Dates, etc. before you executing the sql expression to generate reports or saving it.
   
   The screenshot immediately below was taken after I did that.
   
   Screenshot:
   





4. Saving a SQL Expression for later use: Give the sql expression a Name, key the Name into the Name field, and hit the Save button.
   
   The screen shot immediately below was taken after I did that.
   
   Screenshot:
   





5. Modifying a SQL Expression: Select it by name from the SQL Expressions Drop Down and edit the expression text.
   
   The screenshot immediately below was taken after I did that.
   
   Screenshot:
   


Here's another that I saved under a new name after modifying the expression text to report all three of the ShortDescr codes I used during live play on 05-24-2022 and 05-25-2022.

Screenshot:





6. Using a SQL Expression to Generate Reports:
   
   Select a sql expression from the drop down and make small changes to it (for example ShortDescr code(s) or Date text.)
   
   Alternately key a new sql expression into the SQL Expression textbox or select an existing sql expression from the drop down.
   
   Once you have a valid sql expression in the SQL Expression Tool you can execute it to generate reports.
   
   Before hitting the Execute button you can open up the Factors drop down on the WagerHistory Reports Screen and select a breakout factor.
   
   In the screenshot below I've selected rank for F35.
   
   Screenshot:
   
   
   
   You can also override defaults for your breakout factor by keying in your own Start Range and Interval.
   
   In the screenshot below I've overridden the defaults by keying in 0 for Start Range and 0.025 for Interval.
   
   Screenshot:
   
   
   
   From there, hit the Execute button on the SQL Expression Tool to generate a report.
   
   Here's a link to a text file that contains a cut and paste of the report generated at my end after executing the sql expression shown in the last screenshot above with the data broken out by rank for F35:
   http://www.jcapper.com/messageboard/reports/WagerHist_NewStuffMay2022.txt